package org.libarae.server.databases;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.imageio.ImageIO;

import org.libarae.communication.common.Book;
import org.libarae.communication.common.Message;
import org.libarae.communication.common.MessageThread;
import org.libarae.communication.common.User;
import org.libarae.communication.common.Book.BookStatus;
import org.libarae.communication.common.User.Permission;
import org.libarae.server.Server;


public class mySQL {
	private Connection conn;
	private Statement stat;
	String sql;
	String userName = "root";
	String password = "7ebq109";
	String url = "jdbc:mysql://localhost/test?user="
	                   + userName
	                   + "&password="
	                   + password;
	private static final mySQL INSTANCE = new mySQL();

	private mySQL() {
		try {
			   Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			   conn = DriverManager.getConnection (url);
			   stat = conn.createStatement();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		
	}
	public static mySQL getInstance() {
		return INSTANCE;
	}

	public void createDB() throws SQLException{//creates the DB, send true if sucessfull false if not
		if(!doesTableExist("books")) {
			stat.executeUpdate("CREATE TABLE books (bookID INTEGER PRIMARY KEY AUTOINCREMENT, ownerID INTEGER, title TEXT, isbn TEXT, author TEXT, pubDate TEXT, addedDate UNSIGNED BIG INT, summary TEXT, publisher TEXT, bookStatus INTEGER, picture TEXT, checkOutCount INTEGER, checkedOutBy INTEGER);");
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("Added books table");
		}
		if (!doesTableExist("members")) {
			stat.executeUpdate("CREATE TABLE members (memberID INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT, userName TEXT UNIQUE, password TEXT, userLevel INTEGER, address TEXT, city TEXT, state TEXT, zip TEXT);");
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("Added members table");
			
		}
		if(!doesTableExist("messages")) {
			//system starts counting with 1, so use 0 or -1 userID to represent threads for mods
			stat.executeUpdate("CREATE TABLE messages (messageID INTEGER PRIMARY KEY AUTOINCREMENT, message TEXT, toUser INTEGER, threadID INTEGER, isRead INTEGER, messageTime UNSIGNED BIG INT, isPending INTEGER);");
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("Created the messages DB");
		}	
		if(!doesTableExist("threads")) {
			stat.executeUpdate("CREATE TABLE threads (threadID INTEGER PRIMARY KEY AUTOINCREMENT, userID INTEGER, userID2 INTEGER, subject TEXT, bookID INTEGER, time UNSIGNED BIG INT);");
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("Created the threads DB");
		}
		if(!doesTableExist("serverInformation")) {
			stat.executeUpdate("CREATE TABLE serverInformation(sID INTEGER PRIMARY KEY, message TEXT, isbndbKey TEXT, password TEXT);");
			stat.executeUpdate("INSERT INTO serverInformation(sID, message, isbndbKey, password) VALUES (1, '', '', '');");
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("Create the server Information table");
		}

}
	public void deleteDB() {
		try {
			stat.executeUpdate("drop table if exists books;");
			stat.executeUpdate("drop table if exists members;");
			stat.executeUpdate("drop table if exists messages;");
			stat.executeUpdate("drop table if exists serverInformation;");
			stat.executeUpdate("drop table if exists threads;");
		} catch (SQLException e) {
			if(Server.config.inDebugMode()) { //DEBUG MESSAGE 
				System.out.println("Unable to empty the SQLite DB, here is why: ");
				e.printStackTrace();
			}
		}
		
	}	
	public boolean addBook(String sql, int ownerID, String title, String isbn, String author, String publicationDate, String summary, String publisher, BookStatus bookStatus, String picture, long addedDate) {
		if(doesTableExist("books")) {//verify the table exist
			try {
				PreparedStatement prep = conn.prepareStatement(sql);
				prep.setInt(1, ownerID);
				prep.setString(2, title);
				prep.setString(3, isbn);
				prep.setString(4, author);
				prep.setString(5, publicationDate);
				prep.setLong(6, addedDate);
				prep.setString(7, summary);
				prep.setString(8, publisher);
				prep.setInt(9, bookStatus.ordinal());
				prep.setString(10, picture);
				prep.setInt(11, 0);
				prep.setInt(12, -1);
				prep.executeUpdate();
				return true;
			} catch (SQLException e) {
				if(Server.config.inDebugMode()) {//DEBUG MESSAGE
					System.out.println("Failed to add the book to the database: ");
					e.printStackTrace();
				}
				return false;
			}
		}
		return false;
		
	}
	
	public boolean addUser(String sql, String first, String last, String userName, String password, Permission userLevel, String address, String city, String state, String zip) throws SQLException {
		if(doesTableExist("members")) {
			try {
				//use prepared statements here to prevent sql injection
			    PreparedStatement prep = conn.prepareStatement(sql);
			    prep.setString(1, first);
			    prep.setString(2, last);
			    prep.setString(3, userName);
			    prep.setString(4, password);
			    prep.setInt(5, userLevel.ordinal());
			    prep.setString(6, address);
			    prep.setString(7, city);
			    prep.setString(8, state);
			    prep.setString(9, zip);
			    prep.executeUpdate();
				return true;	
			} catch (SQLException e) {
				if(Server.config.inDebugMode())//DEBUG MESSAGE
					e.printStackTrace();
				if(e.getMessage().toString().contains("is not unique")) {
					return false;
				}
				else {
					throw e;
				}
			}
		}
		throw new SQLException("members table does not exist");
	}
	public User userLogin(String sql, String user, String password) throws SQLException {
		User u;
		try {
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, user);
			prep.setString(2, password);
			ResultSet rs  = prep.executeQuery();
			rs.next();
			u = this.setUser(rs);
			return u;
		} catch (SQLException e) {
			if(e.getMessage().toString().equalsIgnoreCase("ResultSet closed"))
				return null;
			else {
				throw e;
			}
		}
		
	}
	public Book getBook(String sql) {
		Book b;
		try {
			ResultSet rs = stat.executeQuery(sql);
			rs.next();
			b= this.setBook(rs);
			return b;
		} catch (SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE 
				e.printStackTrace();
			return null;
		}
	}
	public User getUser(String sql) {
		User u;
		try {
			ResultSet rs = stat.executeQuery(sql);
			rs.next();
			u =this.setUser(rs);
			return u;
			
		} catch (SQLException e) {
			if(Server.config.inDebugMode()) {//DEBUG MESSAGE
				e.printStackTrace();
				System.out.println("failed to get User");
			}
			return null;
		}			
	}
	public int generalCount(String sql) {
		if(doesTableExist("messages")) {
			try {
				//use prepared statements here to prevent sql injection
				ResultSet rs = stat.executeQuery(sql);
				rs.next();
				return rs.getInt(1);	
			} catch (SQLException e) {
				if(Server.config.inDebugMode()) {//DEBUG MESSAGE
					e.printStackTrace();
					System.out.println("Failed to print unread messages");
				}
				return -1;
			}
		}	
		return -1;
	}
	public boolean setServerInfo(String sql, String variable) {
		 PreparedStatement prep;
		try {
			prep = conn.prepareStatement(sql);
			prep.setString(1, variable);
			prep.executeUpdate();
			return true;
		} catch (SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
		}
		return false;
		    
		
	}
	
	public boolean generalUpdate(String sql) {
		try {
			stat.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			if(Server.config.inDebugMode()) {//DEBUG MESSAGE
				e.printStackTrace();
				System.out.println("failed to update");
			}
			return false;
		}
	}
	public boolean updateBookStatus(String sql, String sql2) {
		try {
			stat.executeUpdate(sql);
			stat.executeUpdate(sql2);
			return true;
		} catch (SQLException e) {
			if(Server.config.inDebugMode()) {//DEBUG MESSAGE
				e.printStackTrace();
				System.out.println("failed to update book status");
			}
			return false;
		}
	}
	
	public boolean generalDelete(String sql) {
		try {
			stat.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
			return false;
		}
	}
	
	public ArrayList<Book> generalBooksSQL(String sql) {
		ArrayList<Book> books = new ArrayList<Book>();
		try {
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()) {
				books.add(this.setBook(rs));
			}
			return books;
		} catch (SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
			if(e.getMessage().toString().equalsIgnoreCase("ResultSet closed"))
				return books;
			else {
				return null;
			}
		}
		
	}
	public ArrayList<User> generalUserSQL(String sql) {
		ArrayList<User> users = new ArrayList<User>();
		try {
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()) {
				users.add(this.setUser(rs));
			}
			return users;
		} catch (SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
			if(e.getMessage().toString().equalsIgnoreCase("ResultSet closed"))
				return users;
			else {
				return null;
			}
		}
	}
	public User getUser(String sql,String userName) {
		if(doesTableExist("members")) {
			try {
				//use prepared statements here to prevent sql injection
			    PreparedStatement prep = conn.prepareStatement(sql);
			    prep.setString(1, userName);
			    ResultSet rs = prep.executeQuery();
			    rs.next();
			    return (this.setUser(rs));	
			} catch (SQLException e) {
				if(Server.config.inDebugMode()) {//DEBUG MESSAGE
					e.printStackTrace();
					System.out.println("failed to get the user with userName: " +userName);
				}
				return null;
			}
		}
		return null;
	}
	
	public ArrayList<Integer> getUsersThread(String sql) {
		ArrayList<Integer> threads = new ArrayList<Integer>();
		try {
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()) {
				threads.add(rs.getInt(1));
			}
			return threads;
		} catch(SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
			return null;
		}
	}
	public String getGeneralServerInfo(String sql) {
		try {
			ResultSet rs = stat.executeQuery(sql);
			if(rs.next()) {
				return rs.getString(1);
			}
		} catch(SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
		}
			return null;
	}
	public boolean updateBook(String sql, Book b, String pic) {
		PreparedStatement prep;
		try {
			prep = conn.prepareStatement(sql);
			prep.setString(1, b.getTitle());
			prep.setString(2, b.getISBN());
			prep.setString(3, b.getAuthor());
			prep.setString(4, b.getPubDate());
			prep.setString(5, b.getSummary());
			prep.setString(6, b.getPublisher());
			prep.setString(7,pic);
			prep.executeUpdate();
			return true;
		} catch(SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
			return false;
		}
	}
	public boolean updateUser(String sql, User u) {
		PreparedStatement prep;
		try {
			prep = conn.prepareStatement(sql);
			prep.setString(1, u.getFirstName());
			prep.setString(2, u.getLastName());
			prep.setInt(3, u.getStatus().ordinal());
			prep.setString(4, u.getAddress());
			prep.setString(5, u.getCity());
			prep.setString(6, u.getState());
			prep.setString(7, u.getZipCode());
			prep.executeUpdate();
			return true;
		} catch(SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
			return false;
		}
	}
	public boolean generalMessageUpdate(String sql) {
		try {
			stat.executeUpdate(sql);
			return true;
		} catch(SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
		}
		return false;
	}
	public boolean addMessageToThread(String sql, String message, int toUser, int ThreadID, boolean isRead, long time, boolean isPending) {
		PreparedStatement prep;
		int messageRead=0;
		int isPending1 = 0;
		if (isRead) 
			messageRead=1;
		if (isRead) 
			isPending1=1;
		
		try {
			prep = conn.prepareStatement(sql);
			prep.setString(1, message);
			prep.setInt(2, toUser);
			prep.setInt(3, ThreadID);
			prep.setInt(4, messageRead);
			prep.setLong(5, time);
			prep.setInt(6, isPending1);
			prep.executeUpdate();
			return true;
		} catch(SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
		}
		return false;
	}
	public boolean addThread(String sql, int user1, int user2, String subject, int bookID) {
		PreparedStatement prep;
		try {
			prep = conn.prepareStatement(sql);
			prep.setInt(1, user1);
			prep.setInt(2, user2);
			prep.setString(3, subject);
			prep.setInt(4, bookID);
			prep.executeUpdate();
			return true;
		} catch(SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
		}
		return false;
	}
	public ArrayList<MessageThread> getThreads(String sql) {
		ArrayList<MessageThread> mt = new ArrayList<MessageThread>();
		try {
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()) {
				mt.add(this.setMessageThread(rs));
			}
			return mt;
		} catch (SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
			if(e.getMessage().toString().equalsIgnoreCase("ResultSet closed"))
				return mt;
			return null;
		}
	}
	public ArrayList<Message> getMessages (String sql) {
		ArrayList<Message> m = new ArrayList<Message>();
		try {
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()) {
				m.add(this.setMessage(rs));
			}
			return m;
		} catch (SQLException e) {
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
			if(e.getMessage().toString().equalsIgnoreCase("ResultSet closed"))
				return m;
			return null;
		}
	}
	public void close() {
		
		//	try {
		//			conn.close();
		//	} catch (SQLException e) {
		//		// TODO Auto-generated catch block
		//		e.printStackTrace();
		//		System.out.println("close failed");
		//	}
	}
	public void open() {
		//try {
		//	if(conn.isClosed()){
		//		   Class.forName ("com.mysql.jdbc.Driver").newInstance ();
		//		   conn = DriverManager.getConnection (url);
		//		   stat = conn.createStatement();
		//	}
	//	} catch (SQLException e) {
			// TODO Auto-generated catch block
	//		e.printStackTrace();
	//	} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
	//		e.printStackTrace();
	//	} catch (InstantiationException e) {
	///		// TODO Auto-generated catch block
	//		e.printStackTrace();
	//	} catch (IllegalAccessException e) {
	//		// TODO Auto-generated catch block
	//		e.printStackTrace();
	//	}
			
	}
	private Permission getUserLevel(int level) {
		switch(level) {
		case 0:
			return Permission.ADMINISTRATOR;
		case 1:
			return Permission.MODERATOR;
		default:
			return Permission.USER;
		}	
	}
	private BookStatus getBookStatus(int status) {
		switch(status) {
		case 0:
			return BookStatus.CHECKED_OUT;
		case 1:
			return BookStatus.PENDING;
		default:
			return BookStatus.AVAILABLE;
		}	
	}
	private Book setBook(ResultSet rs) throws SQLException {
		Book b = new Book();
		BookStatus status  = this.getBookStatus(rs.getInt("bookStatus"));
		b.setBook(rs.getInt("bookID"), rs.getInt("ownerID"), rs.getInt("checkOutCount"), rs.getString("title"), rs.getString("isbn"), rs.getString("author"), rs.getString("pubDate"), rs.getString("addedDate"), rs.getString("summary"), rs.getString("publisher"), status, rs.getInt("checkedOutBy"));
		BufferedImage img = null;
		try {
			File pictureFile = new File(rs.getString("picture"));
			if(pictureFile.exists())
			{
			    img = ImageIO.read(pictureFile);
			}
		    if(img!=null){
		    	b.setPicture(img); 
	
		    }
		    else{
		    	b.setPicture(null);
		    }
		} catch (IOException e) {;
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				e.printStackTrace();
			b.setPicture(null);
		}
	
		return b;
	}
	private User setUser(ResultSet rs) throws SQLException {
		User u = new User();
		Permission currentLevel = getUserLevel(rs.getInt("userLevel"));
		u.setUser(rs.getInt("memberID"), rs.getString("firstName"), rs.getString("lastName"), rs.getString("userName"), "", currentLevel, rs.getString("address"), rs.getString("city"), rs.getString("state"), rs.getString("zip"));
		return u;
	}
	private boolean doesTableExist(String tableName) {
		boolean tableExist = false;
		sql = "SHOW TABLES LIKE '"+tableName+"';";
		try {	
			ResultSet rs = stat.executeQuery(sql);
			if(rs.next() != false) {
				tableExist = true;
			}
		} catch (SQLException e) {
			if(Server.config.inDebugMode()) {//DEBUG MESSAGE 
				System.out.println("Error finding out if "+tableName+" this table exist in the database, printing error: ");
				e.printStackTrace();
			}
		}
	     
	      if (tableExist)
	        return true;
	      else
	        return false;
	    }	
	private Message setMessage(ResultSet rs) throws SQLException {
		boolean read= false;
		boolean isPending = false;
		if(rs.getInt("isPending")==1)
				isPending = true;
		if(rs.getInt("isRead")==1)
				read = true;
		Message m = new Message(rs.getInt("messageID"), rs.getString("message"), rs.getInt("toUser"), rs.getInt("threadID"), read, rs.getLong("messageTime"), isPending);
		return m;
	}
	private MessageThread setMessageThread(ResultSet rs) throws SQLException {
		MessageThread mt = new MessageThread(rs.getInt("threadID"), rs.getInt("userID"), "", rs.getInt("userID2"), "", rs.getString("subject"), rs.getInt("bookID"), rs.getLong("time"), false);
		return mt;
	}

}



